const mysql = require('mysql2/promise');

// 数据库连接配置
const config = {
    host: 'localhost',
    user: 'root',
    password: '20041018Ww!!!',
    database: 'campus_parcel_pickup_service'
};

async function updateAdminSchema() {
    let connection;
    try {
        console.log('开始更新管理员功能数据库结构...');
        
        // 创建数据库连接
        connection = await mysql.createConnection(config);
        
        // 1. 修改role字段，添加admin选项
        console.log('1. 修改role字段，添加admin选项...');
        await connection.execute(`
            ALTER TABLE users MODIFY COLUMN role ENUM('order_user', 'courier', 'both', 'admin') DEFAULT 'order_user'
        `);
        
        // 2. 添加管理员相关字段
        console.log('2. 添加管理员相关字段...');
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN permission_level INT DEFAULT 0 AFTER role`);
            console.log('   - 添加permission_level字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - permission_level字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN account_status VARCHAR(20) DEFAULT 'active' AFTER permission_level`);
            console.log('   - 添加account_status字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - account_status字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN created_by INT NULL AFTER created_at`);
            console.log('   - 添加created_by字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - created_by字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP NULL AFTER created_by`);
            console.log('   - 添加last_login_at字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - last_login_at字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN last_action_at TIMESTAMP NULL AFTER last_login_at`);
            console.log('   - 添加last_action_at字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - last_action_at字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN last_login_ip VARCHAR(50) NULL AFTER last_action_at`);
            console.log('   - 添加last_login_ip字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - last_login_ip字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN failed_login_attempts INT DEFAULT 0 AFTER last_login_ip`);
            console.log('   - 添加failed_login_attempts字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - failed_login_attempts字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN locked_until TIMESTAMP NULL AFTER failed_login_attempts`);
            console.log('   - 添加locked_until字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - locked_until字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        try {
            await connection.execute(`ALTER TABLE users ADD COLUMN password_changed_at TIMESTAMP NULL AFTER password_hash`);
            console.log('   - 添加password_changed_at字段成功');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('   - password_changed_at字段已存在，跳过');
            } else {
                throw error;
            }
        }
        
        // 3. 创建权限表
        console.log('3. 创建权限表...');
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS permissions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL UNIQUE,
                description VARCHAR(255) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        // 4. 创建角色表
        console.log('4. 创建角色表...');
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS admin_roles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL UNIQUE,
                description VARCHAR(255) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        // 5. 创建角色-权限关联表
        console.log('5. 创建角色-权限关联表...');
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS role_permissions (
                role_id INT,
                permission_id INT,
                PRIMARY KEY (role_id, permission_id),
                FOREIGN KEY (role_id) REFERENCES admin_roles(id) ON DELETE CASCADE,
                FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
            )
        `);
        
        // 6. 创建用户-角色关联表
        console.log('6. 创建用户-角色关联表...');
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS user_roles (
                user_id INT,
                role_id INT,
                PRIMARY KEY (user_id, role_id),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (role_id) REFERENCES admin_roles(id) ON DELETE CASCADE
            )
        `);
        
        // 7. 创建管理员操作日志表
        console.log('7. 创建管理员操作日志表...');
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS admin_operation_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                admin_id INT NOT NULL,
                operation_type VARCHAR(50) NOT NULL,
                target_entity VARCHAR(50) NOT NULL,
                target_id INT NULL,
                details TEXT NULL,
                ip_address VARCHAR(50) NULL,
                user_agent TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        
        // 8. 插入基础权限数据
        console.log('8. 插入基础权限数据...');
        const permissions = [
            ['user_view', '查看用户信息'],
            ['user_edit', '编辑用户信息'],
            ['user_create', '创建用户'],
            ['user_delete', '删除用户'],
            ['order_manage', '管理订单'],
            ['courier_manage', '管理代拿员'],
            ['system_config', '系统配置'],
            ['log_view', '查看日志'],
            ['role_manage', '管理角色'],
            ['permission_manage', '管理权限']
        ];
        
        for (const [name, description] of permissions) {
            await connection.execute(
                `INSERT INTO permissions (name, description) VALUES (?, ?) ON DUPLICATE KEY UPDATE description = ?`,
                [name, description, description]
            );
        }
        
        // 9. 插入基础角色数据
        console.log('9. 插入基础角色数据...');
        const roles = [
            ['super_admin', '超级管理员，拥有所有权限'],
            ['user_manager', '用户管理员，管理用户相关功能'],
            ['order_manager', '订单管理员，管理订单相关功能'],
            ['system_viewer', '系统查看者，仅具有查看权限']
        ];
        
        for (const [name, description] of roles) {
            await connection.execute(
                `INSERT INTO admin_roles (name, description) VALUES (?, ?) ON DUPLICATE KEY UPDATE description = ?`,
                [name, description, description]
            );
        }
        
        // 10. 创建初始管理员账户（使用示例哈希密码）
        console.log('10. 创建初始管理员账户...');
        await connection.execute(
            `INSERT INTO users (phone, password_hash, role, permission_level, account_status) VALUES
             ('18888888888', '$2b$10$D8JwXw7ZkL9vN5y6m7o8.uW5z6a7b8c9d0e1f2g3h4i5j6k7l8m9n0', 'admin', 100, 'active')
             ON DUPLICATE KEY UPDATE phone = phone`
        );
        
        // 11. 为超级管理员分配所有权限
        console.log('11. 为超级管理员分配所有权限...');
        await connection.execute(`
            INSERT INTO role_permissions (role_id, permission_id) 
            SELECT ar.id, p.id 
            FROM admin_roles ar, permissions p 
            WHERE ar.name = 'super_admin'
            ON DUPLICATE KEY UPDATE role_id = role_id
        `);
        
        // 12. 将超级管理员角色分配给初始管理员账户
        console.log('12. 将超级管理员角色分配给初始管理员账户...');
        await connection.execute(`
            INSERT INTO user_roles (user_id, role_id) 
            SELECT u.id, ar.id 
            FROM users u, admin_roles ar 
            WHERE u.phone = '18888888888' AND ar.name = 'super_admin'
            ON DUPLICATE KEY UPDATE user_id = user_id
        `);
        
        console.log('🎉 管理员功能数据库更新完成！');
        console.log('📝 初始管理员账户信息：');
        console.log('   手机号：18888888888');
        console.log('   密码（示例哈希，请在实际使用中修改）：admin123');
        console.log('   角色：admin');
        
    } catch (error) {
        console.error('❌ 数据库更新失败：', error.message);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// 执行更新
updateAdminSchema().catch(err => {
    console.error('更新脚本执行失败');
    process.exit(1);
});